SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[Main_Get_MenuByRole]
@UserName VARCHAR(150)
AS
DECLARE @RoleId INT 
IF (SELECT RoleName FROM Media_Accounts.dbo.aspnet_Roles
	INNER JOIN Media_Accounts.dbo.aspnet_UsersInRoles ON Media_Accounts.dbo.aspnet_Roles.RoleId = Media_Accounts.dbo.aspnet_UsersInRoles.RoleId
	INNER JOIN Media_Accounts.dbo.aspnet_Users ON Media_Accounts.dbo.aspnet_UsersInRoles.UserId = Media_Accounts.dbo.aspnet_Users.UserId
	WHERE UserName = @UserName) = 'Administrator'
BEGIN
	SET @RoleId = 3
END 
ELSE IF (SELECT RoleName FROM Media_Accounts.dbo.aspnet_Roles
		INNER JOIN Media_Accounts.dbo.aspnet_UsersInRoles ON Media_Accounts.dbo.aspnet_Roles.RoleId = Media_Accounts.dbo.aspnet_UsersInRoles.RoleId
		INNER JOIN Media_Accounts.dbo.aspnet_Users ON Media_Accounts.dbo.aspnet_UsersInRoles.UserId = Media_Accounts.dbo.aspnet_Users.UserId
		WHERE UserName = @UserName) = 'Power User'
BEGIN
	SET @RoleId = 2
END 
ELSE
BEGIN
	SET @RoleId = 1
END 														
SELECT MenuText, PageName, ControllerName 
FROM Main_TopNavMenu
WHERE MenuText = 'Home' AND isActive = 1 AND RoleId <= @RoleId
UNION ALL 
SELECT MenuText, PageName, ControllerName 
FROM Main_TopNavMenu
WHERE MenuText NOT IN ('Home', 'About') AND isActive = 1 AND RoleId <= @RoleId
UNION ALL 
SELECT MenuText, PageName, ControllerName 
FROM Main_TopNavMenu
WHERE MenuText = 'About' AND isActive = 1 AND RoleId <= @RoleId
GO
